package com.maycurobj.repository.oa;

import com.maycurobj.entity.oa.ChuChaiEntity;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface WorkflowByChuChaiRepository extends JpaRepository<ChuChaiEntity,Integer> {

    /**
     * 出差流程
     * sql 获取 工号对应流程列表 待分页
     * @param employeeId
     * @return
     */
    @Query(nativeQuery = true,value = "select distinct * from ( select DISTINCT biaoti ||'-'|| LIUCHENGBH AS biaoti,LIUCHENGBH,to_date(shenqingrq,'yyyy-mm-dd') as shenqingrq,caozuor,jine,requestid from (SELECT DISTINCT ( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,0.00 AS jine,f47.REQUESTID as REQUESTID FROM formtable_main_47 f47,workflow_currentoperator, HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId ) ) where biaoti like %:keyWord% order by requestid desc ")
    List<ChuChaiEntity> getWFs(@Param(value = "employeeId") String employeeId, @Param(value="keyWord") String keyWord,Pageable pageable);

    /**
     * 获取全部记录(出差流程)
     */
    @Query(nativeQuery = true,value = " select count(*) as counts from ( select DISTINCT biaoti ||'-'|| LIUCHENGBH AS biaoti,LIUCHENGBH,to_date(shenqingrq,'yyyy-mm-dd') as shenqingrq,caozuor,jine,requestid from (SELECT DISTINCT ( SELECT wr.requestname FROM WORKFLOW_REQUESTBASE wr WHERE wr.requestid = f47.requestid ) AS biaoti,f47.liuchengbh,f47.shenqingrq,lastname AS caozuor,0.00 AS jine,f47.REQUESTID as REQUESTID FROM formtable_main_47 f47,workflow_currentoperator, HrmResource h WHERE workflow_currentoperator.Requestid = f47.requestid AND workflow_currentoperator.userid = h.id AND iscomplete = 1 AND workcode = :employeeId ) ) where biaoti like %:keyWord% ")
    int getCounts(@Param(value = "employeeId") String employeeId,@Param(value="keyWord") String keyWord);

}
